using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;
using test8_h1.Models;

namespace test8_h1.Data
{
    public class DatabaseHelper
    {   
        private static string dbPath = "StudentManagement.db";
        private static string connectionString = $"Data Source={dbPath};Version=3;";

        public static void InitializeDatabase()
        {
            if (!File.Exists(dbPath))
            {
                SQLiteConnection.CreateFile(dbPath);
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    string sql = @"
                        CREATE TABLE School (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            Address TEXT
                        );

                        CREATE TABLE Class (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            Grade INTEGER,
                            SchoolId INTEGER,
                            FOREIGN KEY(SchoolId) REFERENCES School(Id)
                        );

                        CREATE TABLE Student (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            Age INTEGER,
                            Gender TEXT,
                            ClassId INTEGER,
                            FOREIGN KEY(ClassId) REFERENCES Class(Id)
                        );

                        CREATE TABLE Log (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Operation TEXT NOT NULL,
                            Details TEXT,
                            Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                        );";

                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public static void LogOperation(string operation, string details)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "INSERT INTO Log (Operation, Details) VALUES (@Operation, @Details)";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Operation", operation);
                    command.Parameters.AddWithValue("@Details", details);
                    command.ExecuteNonQuery();
                }
            }
        }

        public static List<Log> GetLogs()
        {
            var logs = new List<Log>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM Log ORDER BY Timestamp DESC";
                using (var command = new SQLiteCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        logs.Add(new Log
                        {
                            Id = reader.GetInt32(0),
                            Operation = reader.GetString(1),
                            Details = reader.GetString(2),
                            Timestamp = reader.GetDateTime(3)
                        });
                    }
                }
            }
            return logs;
        }
    }
}